此篇文章同步發表於個人部落格

昨天有介紹資料的類型，在那麼多種類型當中，依照預計要存入的資料，給予適當的類型設定，才能確保資料的正確性。

今天來談談一些注意事項

數字

小數點差別

-- 建立一格表 包含三種小數類型
CREATE TABLE number_types (
    numeric_column numeric(20,5),
    real_column real,
    double_column double precision
);

-- 插入資料
INSERT INTO number_types
VALUES
    (.8, .8, .8),
    (1.23456, 1.23456, 1.23456),
    (0.78951384, 0.78951384, 0.78951384),
    (1.78951384, 1.78951384, 1.78951384);

-- 查詢出結果
SELECT * FROM number_types;

可以看到numeric 定點數會把不足的位數全部補0。

第三筆 real_column 因為欄位設定real最多顯示6精度，小數點前面如果是0不算，所以顯示種共七個數字，並且超過的四捨五入進位。

第四筆 real_column 因為整數位是有數字，跟第三筆的資料有差別，只顯示六個數字。

double_column 欄位 因為設定 double precision 所以可以把資料全部顯示出來，資料沒有大於15位數精度。

double precision 的誤差

比如說在存入經緯度的資料時 使用 double precision ，因為存入資料可能大於欄位的範圍會有一些誤差

（實際上資料可能 “0.099999999999999999999999999” 而不是 “0.1” ）

假設資料庫存放的精度為 120.136203964611

-- 這樣是查不到資料的
SELECT * FROM map
WHERE longitude = 120.136203964611;

--解決辦法一：把誤差考慮進去
SELECT * FROM map
where longitude between 120.136203964610 and 120.136203964612;

參考 Stack Overflow 解答：https://stackoverflow.com/questions/8548681/postgresql-query-returning-zero-rows-with-double-precision-field-function

數字資料類型選擇

• 如果資料可以用整數就用整數，依照大小去決定儲存類型
• 如果資料一定有小數點
  • 不能有誤差的話，例如金額資料，需要去計算的話，建議使用定點數numeric或decimal。
  • 如果數值位數太大，務必要選擇足夠儲存的大小，請用 double precision(可能會有誤差)

日期時間

timestamp 資料類型適用於 紀錄操作時間，比如說 結帳時間、通常還會加上 with time zone 來記錄時區(如果你做的是國際企業，這個很需要)

interval 資料類型我不常用，但是最近發現這是一個不錯的東西，它是用來記錄時間的長度 例如：早上9點打卡，下午6點打卡，欄位內會紀錄 9hours， 官方文件可以看到各式各樣的單位。

第11版官方文件－時間類型 https://www.postgresql.org/docs/11/datatype-datetime.html